【Redshift 入門】Redshiftのスキーマ別権限周りの検証をやってみた

みなさん、こんにちは。コンサル部のテウです。

ビックデータ分析をするなら一番先に検討するサービスは、恐らく Redshift じゃないかと思うほど、Redshift は便利ですし、有用ななサービスであることに間違いないですよね。なので、今日は Redshiftを入門してみたので、Redshiftの入門ブログとしてスキーマ別の権限付与について話させて頂きますー！

それでは早速始めます！

目次

やってみた

Redshift立ち上げ

最近Redshiftのマネコンが新しく変更されましたね。Dashboard メニューから右側の Create Cluster ボタンをクリックします。

今回は一番安い dc2.large を選択して Nodes は 1 に変更しました。Redshiftの価格は秒単位ではなく時間単位なので、一回実行しちゃうとすぐ消しても1時間の価格を請求されますので、こちらに関してはご注意ください。例えば、1分だけ ds.8xlarge のクラスターを2台稼働してすぐ消す場合でも、$9.52 x 2 = $19.04 （約2000円）を課金します。Redshiftの検証は計画的にする必要があるってことですね。

次の Cluster Details はデフォルトでそのまま置いて、パスワードを設定しました。

右下の Create Cluster ボタンをクリックして Redshift Cluster を生成します。

少しお待ちして、下のように Available になってから進めます。

Editor にて Cluster に接続

dc.large 系の Cluster は Editor 機能が使えます。現状 Editor にて接続が可能な Cluster タイプは以下のようです。

• DC1.8xlarge
• DC2.large
• DC2.8xlarge
• DS2.8xlarge

左側のメニューから Editor を選択して、先ほど作った接続情報を入力します。

Connect to Database をクリックすると、下のような Query を実行できる画面が表示されます。

Database 生成

一番最初に作ることは検証用 Database です。下のような Query を入力して実行します。

create database classmethod_sample_db;

User生成

今回はユーザーを作ります。権限の検証を行うため、2名分のユーザーを作ります。現状、Editor にて同時に実行できる Query 数は一回１Queryの制限があるため、一回に1個ずつ作ります。

create user cm_user1 password 'Hello123';
create user cm_user2 password 'World456';

cm_user2 ユーザーも作ります。

あと、Editor の画面で実行することは単純な作業なので、これからは SQL Query だけ記述します。

接続中の Database の切り替え

先ほど作った classmethod_sample_db に接続して、Database 配下の Schema を作ります。右上の Change connection ボタンをクリックして、以下のように Create new connection を選択します。

先ほど作った Database 名とマスターユーザー接続情報を入力します。

接続したら右上の接続情報が更新されます。

Schema 生成

今回は Schema を生成します。Schema はユーザーに権限を与える形で作ります。Query は以下のように入力して一個ずつ実行します。

create schema cm_schema1 authorization cm_user1;
create schema cm_schema2 authorization cm_user2;

cm_user1 は cm_schema2 に対して Query の実行が不可能になり、cm_user2 は逆に cm_schema1 に対して Query の実行が不可能になります。本記事ではこの辺りの検証を行います。

ユーザーの切り替え (cm_user1)

上と同じように Connection を変更します。今回は cm_user1 の情報を使って classmethod_sample_db に接続します。

接続したら右上の接続情報が更新されます。

Table 生成 (cm_schema1)

今回は cm_user1 にて cm_schema1 の配下に sample_tbl を生成します。

create table cm_schema1.sample_tbl (id int);

はい、正常に作られました。次は cm_schema2　の配下に同じく Table を作ろうとしたらどうなるのかを確認してみます。

create table cm_schema2.sample_tbl (id int);

すると、以下のように permission denied for schema cm_schema2; エラーが出てきて、権限がなくて実行できませんというメッセージが表示されます。

Table の生成だけではなく、Select 等の Query は実行可能なのかを確認しやすくするために dummy データを入れておきます。

insert into cm_schema1.sample_tbl values(1);
insert into cm_schema1.sample_tbl values(2);
insert into cm_schema1.sample_tbl values(3);

データが正常に入ったかを確認してみます。

select * from cm_schema1.sample_tbl;

Query results のところに実行結果が表示されます。スクロールを少し下げるとよく見えます。

ユーザーの切り替え (cm_user2)

今回は cm_user2 に切り替えます。

右上に cm_user2 に更新されたかを確認しておきます。

Table 生成 (cm_schema2)

上の cm_schema1.sample_tbl のように同じく cm_schema2 の配下にも sample_tbl を作ります。

create table cm_schema2.sample_tbl (id int);

次は同じく cm_schema1 の配下に Table を作ろうとしたらどうなるのかを確認してみます。

create table cm_schema1.sample_tbl2 (id int);

はい、ダメでした。

dummy データを生成します。

insert into cm_schema2.sample_tbl values(1);
insert into cm_schema2.sample_tbl values(2);
insert into cm_schema2.sample_tbl values(3);

確認します。

select * from cm_schema2.sample_tbl;

はい、よく入ってありますね。

権限が無い Schema 配下の Table にクエリ

次は cm_user2 から cm_schema1.sample_tbl に select クエリを実行してみます。

select * from cm_schema1.sample_tbl;

はい、できませんでした。

【TIP】ユーザー別の権限を確認するクエリ

実はですね、いちいちこういう風に権限を確認しなくても、ユーザー別にどの Schema に権限があるかどうかを確認できるクエリがあって、紹介させて頂きます。これ使って権限を確認することは本当に楽ですね。

SELECT
    u.usename,
    s.schemaname,
    has_schema_privilege(u.usename,s.schemaname,'create') AS user_has_select_permission,
    has_schema_privilege(u.usename,s.schemaname,'usage') AS user_has_usage_permission
FROM
    pg_user u
CROSS JOIN
    (SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
    u.usename = 'cm_user1'
;

最後の WHERE 条件で u.username 値を調べたいユーザー名に置換するだけで、そのユーザーの権限の調査が可能です。

この Query は cm_user2 の権限で実行したのですが、pg_user と pg_tables は pg_catalog というデフォルトで提供される Schema の配下の Table ですので、どのユーザーでも基本アクセスが可能です。（正確には以下の結果画面から確認できる user_has_usage_permission が true の場合のみですが、デフォルトではこの値が true になっています。）

Query 結果は以下のようです。

2ページを見ると、本記事で作った cm_schema1 と cm_schema2 の権限が見えるでしょう。

最後に

Redshift を検証することは費用もかなり掛かりますし、なかなか怖いですよね。そのため、今後も Redshift で何かを検証したらすぐアウトプットできるようにガンガンブログ化して行きたいと思います！

以上、コンサル部のテウでした。